package cn.xshi.common.poi;

import cn.xshi.common.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Desc excel工具类
 * @Author 邓纯杰
 * @CreateTime 2012-12-12 12:12:12
 */
@Slf4j
public class ExcelUtil {

    /**
     * 执行命令
     * @param command
     */
    public static int exec(String command) {
        try {
            Process process = Runtime.getRuntime().exec(command);
            InputStream inputStream = process.getInputStream();
            BufferedReader bufferReader = new BufferedReader(new InputStreamReader(inputStream, "GBK"));
            String readline;
            List results = new ArrayList();
            while ((readline = bufferReader.readLine()) != null) {
                results.add(readline);
                log.info("execute command result : " + readline);
            }
            int status = process.waitFor();
            return status;
        }catch (Exception e){
            log.error("execute command error:{}",e);
        }
        return -1;
    }

    /**
     * Excel 导出
     * @param response  HttpServletResponse
     * @param header 表头
     * @param keys map的key值
     * @param content 内容数据
     * @param title 表格名字
     * @param sheetName sheet名
     */
    public static void exportExcel(HttpServletResponse response, String[] header, String[] keys, List<Map<String, Object>> content, String title, String sheetName){
        ServletOutputStream outputStream = null;
        try {
            title = title + ".xlsx";
            Workbook workbook = new SXSSFWorkbook(1000);
            Sheet sheet = workbook.createSheet(sheetName);
            Row row = sheet.createRow( 0);
            // 行高
            row.setHeight((short) 700);
            // 列宽
            for (int i = 0; i < header.length; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }
            for (int i = 0; i < header.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(header[i]);
                cell.setCellStyle(HeaderStyle(workbook));
            }
            for (int i = 0; i < content.size(); i++) {
                Map<String, Object> map = content.get(i);
                row = sheet.createRow((int) i + 1);
                row.setHeight((short) 500);
                for (int j = 0; j < keys.length; j++){
                    Cell cell = row.createCell(j);
                    cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
                    cell.setCellStyle(contentStyle(workbook));
                }
            }
            title = new String(title.getBytes("UTF-8"), "ISO8859-1");
            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Content-Disposition", "attachment; filename=" + title);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        }catch (Exception e){
            log.error("导出Excel异常：",e);
        }finally {
            if(null != outputStream){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("导出Excel后关闭流异常：",e);
                }
            }
        }
    }

    /**
     * Excel 导入
     * @param file 文件
     * @param keys 数据顺序
     */
    public static  List<Map<String, Object>>  importExcel(MultipartFile file, String[] keys) throws Exception{
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(file.getInputStream());
            workbook = new HSSFWorkbook(poifsFileSystem);
        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            Row row = sheet.getRow(i + 1);
            Map<String,Object> tmp = new HashMap<>();
            for (int j = 0;j < keys.length; j++){
                Cell cell = row.getCell(j);
                // 把类型转行Spring
//                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
            result.add(tmp);
        }
        return result;
    }

    /**
     * 表头样式
     */
    private static CellStyle HeaderStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 内容样式
     */
    private static CellStyle contentStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 公共样式
     */
    private static CellStyle commonStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        return style;
    }

    /**
     * string转换成short类型
     * @param object
     * @return
     */
    public static Short toShort(Object object) {
        String val = String.valueOf(object);
        Short s = Short.valueOf(val);
        return s;
    }

    /**
     * 根据正则表达式，从字符串提取数字
     * @param text
     * @return
     */
    public int getNumberByPattern(String text){
        String regEx="[^0-9]";
        int number;
        if((text==null)||(text.equals(""))){
            number = 0;
        }else{
            Pattern p = Pattern.compile(regEx);
            Matcher m = p.matcher(text);
            String str = m.replaceAll("").trim();
            if((str==null)||(str.equals(""))){
                number = 0;
            }else{
                number = Integer.valueOf(str);
            }
        }
        return number;
    }

    /**
     * 获取日期格式单元格的样式
     * @return 单元格样式
     */
    private CellStyle getDateCellStyle(Workbook workbook) {
        CellStyle CellStyleDate = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        CellStyleDate.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));
        // 背景色的设定 自动换行
        CellStyleDate.setWrapText(true);
        // 设置字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        CellStyleDate.setFont(font);
        // 水平居中,垂直居中
        CellStyleDate.setAlignment(HorizontalAlignment.CENTER);
        CellStyleDate.setVerticalAlignment(VerticalAlignment.CENTER);
        CellStyleDate.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        CellStyleDate.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return CellStyleDate;
    }


    /**
     * 设置边框样式
     * @param xssfWorkbook XSSFWorkbook对象
     * @param xSheet XSSFSheet对象
     * @param row 行
     * @param size 多少条（包括本数字）
     */
    public void setBorder(XSSFWorkbook xssfWorkbook, XSSFSheet xSheet, int row, int size){
        XSSFCellStyle xssfCellStyle = xssfWorkbook.createCellStyle();
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        XSSFRow rows = xSheet.getRow(row) == null ? xSheet.createRow(row) : xSheet.getRow(row);
        for (int i = 0; i <= size;i++){
            if(rows.getCell(i) == null){//空则创建
                rows.createCell(i).setCellStyle(xssfCellStyle);
            }else{
                rows.getCell(i).setCellStyle(xssfCellStyle);
            }
        }
    }

    /**
     * 关闭输出流
     * @param os
     */
    private void close(OutputStream os) {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
                log.info("关闭输出流异常:" + e);
            }
        }
    }

    /**
     * 合并单元格
     * @param workbook
     * @param mergeRowBegin 开始合并的行
     * @param columns 需要合并的列
     */
    public static <T> void mergeCells(List<T> obj, String sheetName, HSSFWorkbook workbook, int mergeRowBegin, Integer... columns){
        HSSFSheet sheet = null;
        if(StringUtil.isEmpty(sheetName)){
            workbook.getSheetAt(0);//取第一个sheet
            workbook.getSheet("");
        }else{
            workbook.getSheet(sheetName);
        }
        String[] str = new String[obj.size()];
        for (Integer column : columns) {
            int rowBegin = mergeRowBegin;
            //获取指定列，不同行的单元格值
            try {
                for (int i = 0; i < obj.size(); i++) { //遍历行
                    HSSFRow row = sheet.getRow(rowBegin);
                    HSSFCell cell = row.getCell(column);
                    String value = cell.getStringCellValue();
                    str[i] = value;
                    rowBegin++;
                }
            } catch (Exception e) {
                log.info("获取单元格值失败:" + e);
            }
            //合并
            try {
                int index = 0;//计数器
                for (int i = 0; i < str.length - 1; i++) {
                    index++;//1 2 3
                    //判断单元格值是否相同
                    if (!str[i].equals(str[i + 1])) {
                        if (1 - index != 0) { //如果只有一个单元格，不合并
                            CellRangeAddress cellAddresses = new CellRangeAddress(i + 1 - index + mergeRowBegin, i + mergeRowBegin, column, column);
                            sheet.addMergedRegion(cellAddresses);
                            index = 0;
                        }

                    }
                    //判断是否循环到最后一行
                    if (i + 1 == str.length - 1) {
                        if ( index != 0) {//如果只有一个单元格，不合并
                            CellRangeAddress cellAddresses = new CellRangeAddress(i + 1 - index + mergeRowBegin, i + 1 + mergeRowBegin, column, column);
                            sheet.addMergedRegion(cellAddresses);
                        }
                    }
                }
            } catch (Exception e) {
                log.info("合并单元格失败:" + e);
            }
        }
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     * @param sheet 要设置的sheet.
     * @param textList 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(textList);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

    /**
     * 生成下拉框
     * @param rowNumber 目标行号
     * @param colNumber 目标列号
     * @param sheet sheet
     * @param list  下拉框数据
     */
    public void generateSelectBox(int rowNumber, int colNumber, Sheet sheet, List<String> list) {
        //生成下拉列表
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowNumber, rowNumber, colNumber, colNumber);

        DataValidation dataValidation;

        XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);

        DataValidationConstraint dataValidationConstraint = new XSSFDataValidationConstraint(list.toArray(new String[0]));

        dataValidation = helper.createValidation(dataValidationConstraint, cellRangeAddressList);

        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置单元格上提示
     * @param sheet 要设置的sheet.
     * @param promptTitle 标题
     * @param promptContent 内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     * @return 设置好的 sheet.
     */
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) {
        // 构造constraint对象
        DVConstraint dvConstraint = DVConstraint.createCustomFormulaConstraint("BB1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation hssfDataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        hssfDataValidation.createPromptBox(promptTitle, promptContent);
        hssfDataValidation.setSuppressDropDownArrow(false);
        hssfDataValidation.createPromptBox("输入提示", "请从下拉列表中选择！");
        hssfDataValidation.setShowPromptBox(true);
        hssfDataValidation.setErrorStyle(100);
        hssfDataValidation.setShowErrorBox(true);
        sheet.addValidationData(hssfDataValidation);
        return sheet;
    }
}
